﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace Healthy
{
    public partial class frmperson : Form
    {
        String SEX;
        public frmperson()
        {
            InitializeComponent();
        }

        private void frmperson_Load(object sender, EventArgs e)
        {
            string oMySqlConn = (@"Server=narma-pc\sqlexpress;Database=mydata;Integrated Security=SSPI");
            SqlConnection Conn = new SqlConnection(oMySqlConn);

            showType();
        }
        private void showType()
        {
            string oMySqlConn = (@"Server=narma-pc\sqlexpress;Database=mydata;Integrated Security=SSPI");
            SqlConnection Conn = new SqlConnection(oMySqlConn);
            Conn.Open();

            string sqlType;
            sqlType = "select * from school ";

            SqlDataAdapter da;
            DataSet ds = new DataSet();
            da = new SqlDataAdapter(sqlType, Conn);
            da.Fill(ds, "school");

            idsch.DataSource = ds.Tables["school"];
            idsch.ValueMember = "SCHID";
            idsch.DisplayMember = "SCHNAM";
        }
        private void button1_Click(object sender, EventArgs e)
        {
            string oMySqlConn = (@"Server=narma-pc\sqlexpress;Database=mydata;Integrated Security=SSPI");
            SqlConnection Conn = new SqlConnection(oMySqlConn);
            Conn.Open();

            if (hn.Text != "" && name.Text != "" && idcard.Text != "" && add.Text != "" && cmbstr.Text != "" && nation.Text != "" && race.Text != "" && relig.Text != "" && occu.Text != "" && fth.Text != ""&& fmo.Text != "")
            {

                //string strOut = "";
                //strOut += "เลขที่ผู้มาใช้บริการ :" + hn.Text + "\n";
                //strOut += "ชื่อผู้มาใช้บริการ :" + name.Text + "\n";
                //strOut += "วันเกิด :" + bdate.Value.ToLongDateString() + "\n";
                //strOut += "เพศ :" + SEX + "\n";
                //strOut += "เลขที่บัตรประชาชน  :" + idcard.Text + "\n";
                //strOut += "วันเดือนปีที่มาครั้งแรก :" + date.Value.ToLongDateString() + "\n";
                //strOut += "ที่อยู่ :" + add.Text + "\n";
                //strOut += "โทรศัพท์ :" + mtel.Text + "\n";
                //strOut += "สภาพสมรส :" + cmbstr.SelectedValue + "\n";
                //strOut += "สัญชาติ:" + nation.Text + "\n";
                //strOut += "เชื่อชาติ :" + race.Text + "\n";
                //strOut += "ศาสนา  :" + relig.Text + "\n";
                //strOut += "อาชีพ :" + occu.Text + "\n";
                //strOut += "ชื่อบิดา :" + fth.Text + "\n";
                //strOut += "ชื่อมารดา:" + fmo.Text + "\n"; 
                //strOut += "ชื่อโรงเรียน(ในกรณีที่เป็นนักเรียน)  :" + idsch.SelectedValue + "\n";

                //MessageBox.Show(strOut, "ผลการทำงาน", MessageBoxButtons.OK, MessageBoxIcon.Information);

                string strdate = Convert.ToString(bdate.Value.Year) + "/" + Convert.ToString(bdate.Value.Month) + "/" + Convert.ToString(bdate.Value.Day);//วันที่
                string strdate1 = Convert.ToString(date.Value.Year) + "/" + Convert.ToString(date.Value.Month) + "/" + Convert.ToString(date.Value.Day);//วันที่
                string query = "insert into person(HN,PNNAM,BIRTH,SEX,IDCARD,DATFST,ADDRESS,TEL,STMRRY,NATION,RACE,RELIGN,OCCUPT,FTHNAM,MTHNAM,SCHID) values('" + hn.Text + "' ,'" + name.Text + "','" + strdate + "','" + SEX + "','" + idcard.Text + "','" + strdate1 + "','" + add.Text + "' ,'" + mtel.Text + "','" + cmbstr.Text + "' ,'" + nation.Text + "' ,'" + race.Text + "','" + relig.Text + "','" + occu.Text + "','" + fth.Text + "','" + fmo.Text + "','" + idsch.SelectedValue + "' )";

                SqlCommand cmd7 = new SqlCommand(query, Conn);
                cmd7.ExecuteNonQuery();

                string sql = "SELECT * FROM person";
               
                SqlCommand cmd1 = new SqlCommand(sql, Conn);
                SqlDataAdapter odbcDA = new SqlDataAdapter(cmd1);
                DataSet ds = new DataSet();
                odbcDA.Fill(ds);
                this.dataGridView1.DataSource = ds.Tables[0];
               
                Formet();
            }
            else
            {
                MessageBox.Show("ข้อมูลไม่ครบ", "ผิดพลาด");
            }
            //MessageBox.Show("ข้อมูลถูกบันทึกเรียบร้อยแล้ว");
        }

        private void r1_CheckedChanged(object sender, EventArgs e)
        {
            SEX = " ชาย ";
        }

        private void r2_CheckedChanged(object sender, EventArgs e)
        {
            SEX = " หญิง ";
        }

        

        private void button3_Click(object sender, EventArgs e)
        {
            Close();
        }
        private void cleardata()
        {
            hn.Text= "";
            name.Text = "";
            bdate.Text= "";
            SEX = "";
            idcard.Text= "";
            date.Text= "";
            add.Text= "";
            mtel.Text= "";
            cmbstr.SelectedValue = "";
            nation.Text = "";
            race.Text = "";
            relig.Text = "";
            occu.Text= "";
            fth.Text= "";
            fmo.Text = "";
            idsch.SelectedValue = "";
        }

        private void button2_Click(object sender, EventArgs e)
        {
            cleardata();
        }

        private void button4_Click(object sender, EventArgs e)
        {
            string oMySqlConn = (@"Server=narma-pc\sqlexpress;Database=mydata;Integrated Security=SSPI");
            SqlConnection Conn = new SqlConnection(oMySqlConn);
            Conn.Open();


            string sql1 = "SELECT HN,PNNAM,BIRTH,SEX,IDCARD,DATFST,ADDRESS,TEL,STMRRY,NATION,RACE,RELIGN,OCCUPT,FTHNAM,MTHNAM,SCHNAM FROM person,school where person.SCHID=school.SCHID";
            SqlCommand cmd1 = new SqlCommand(sql1, Conn);
            SqlDataAdapter odbcDA = new SqlDataAdapter(cmd1);
            DataSet ds = new DataSet();

            odbcDA.Fill(ds);
            this.dataGridView1.DataSource = ds.Tables[0];
           
            Formet();
        }
        private void Formet()
        {
            
            DataGridViewCellStyle cs = new DataGridViewCellStyle();
            cs.Font = new Font("MS Sans Serif", 14, FontStyle.Regular);
            this.dataGridView1.ColumnHeadersDefaultCellStyle = cs;



            this.dataGridView1.Columns[0].HeaderText = "เลขที่ HN";
            this.dataGridView1.Columns[1].HeaderText = "ชื่อผู้มาใช้บริการ";
            this.dataGridView1.Columns[2].HeaderText = "วันเกิด";
            this.dataGridView1.Columns[3].HeaderText = "เพศ ";
            this.dataGridView1.Columns[4].HeaderText = "เลขที่บัตรประชาชน ";
            this.dataGridView1.Columns[5].HeaderText = "วันเดือนปีที่มาครั้งแรก";
            this.dataGridView1.Columns[6].HeaderText = "ที่อยู่ ";
            this.dataGridView1.Columns[7].HeaderText = "โทรศัพท์ ";
            this.dataGridView1.Columns[8].HeaderText = "สภาพสมรส";
            this.dataGridView1.Columns[9].HeaderText = "สัญชาติ";
            this.dataGridView1.Columns[10].HeaderText = "เชื่อชาติ ";
            this.dataGridView1.Columns[11].HeaderText = "ศาสนา";
            this.dataGridView1.Columns[12].HeaderText = "อาชีพ ";
            this.dataGridView1.Columns[13].HeaderText = "ชื่อบิดา";
            this.dataGridView1.Columns[14].HeaderText = "ชื่อมารดา";
            this.dataGridView1.Columns[15].HeaderText = "ชื่อโรงเรียน(ในกรณีที่เป็นนักเรียน)";

            this.dataGridView1.Columns[0].Width = 100;
            this.dataGridView1.Columns[1].Width = 150;
            this.dataGridView1.Columns[2].Width = 80;
            this.dataGridView1.Columns[3].Width = 100;
            this.dataGridView1.Columns[4].Width = 100;
            this.dataGridView1.Columns[5].Width = 200;
            this.dataGridView1.Columns[6].Width = 100;
            this.dataGridView1.Columns[7].Width = 100;
            this.dataGridView1.Columns[8].Width = 100;
            this.dataGridView1.Columns[9].Width = 100;
            this.dataGridView1.Columns[10].Width = 100;
            this.dataGridView1.Columns[11].Width = 100;
            this.dataGridView1.Columns[12].Width = 100;
            this.dataGridView1.Columns[13].Width = 150;
            this.dataGridView1.Columns[14].Width = 150;
            this.dataGridView1.Columns[15].Width = 150;
          
        }

    }
}
